﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Modellayer;


namespace Dblayer
{
    public class DbShow
    {
        SqlConnection con;
        DbShowRoom dbSR;
        DbMovie dbM;


        public DbShow()
        {
            con = new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dm76_4;Persist Security Info=True;User ID=DM76_4;Password=MaaGodt");
            dbSR = new DbShowRoom();
            dbM = new DbMovie();
        }

        public int getShowId()
        {
            int maxValue = 0;
            try
            {
                if (con == null)
                {
                    con.Open();
                }
                string getMax = @"SELECT MAX(showId) FROM Show";
                SqlCommand myCommand = new SqlCommand(getMax, con);
                maxValue = (int)myCommand.ExecuteScalar();
                return maxValue;
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);

            }
            return maxValue;
        } 

        public void createShow(Show s)
        {
            try
            {
                con.Open();
                string insert = @"insert into Show(ShowId, ShowTime, showRoomNumber, Title) values ('" + s.showId + "','" + s.showTime.ToString() + "','" + s.showRoom.showRoomNumber + "','" + s.movie.Title+ "')";
                SqlCommand myCommand = new SqlCommand(insert, con);
                myCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        public Show findShow(int showId)
        {
            Show s = new Show();
            SqlDataReader rdr = null;
            try
            {
                con.Open();
                string findQuery = @"select * from Show where ShowId = '" + showId + "'";
                SqlCommand myCommand = new SqlCommand(findQuery, con);
                rdr = myCommand.ExecuteReader();
                while (rdr.Read())
                {
                    s.showId = int.Parse(rdr["ShowId"].ToString());
                    string dato = rdr["ShowTime"].ToString();
                    DateTime dt = new DateTime(int.Parse(dato.Trim().Substring(6, 4)), int.Parse(dato.Trim().Substring(3, 2)), int.Parse(dato.Trim().Substring(0, 2)), int.Parse(dato.Trim().Substring(11, 2)), int.Parse(dato.Trim().Substring(14, 2)), int.Parse(dato.Trim().Substring(17, 2)));
                    s.showTime = dt;
                    s.showRoom = dbSR.findShowRoom(rdr["showRoomNumber"].ToString());
                    s.movie = dbM.findMovie(rdr["Title"].ToString());
                }

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                s = null;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }


                if (con != null)
                {
                    con.Close();
                }

            }

            return s;
        }


    }
}
